USE [exportDb]
GO

/****** Object:  View [dbo].[View_ProductSummary]    Script Date: 07/03/2014 23:44:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[View_ProductSummary]
AS

SELECT A.ProductCode AS MaterialNo
		,D.Qty AS ActualQty
		,A.QueueMappedQty AS ComfirmedQty
		,A.QueueQty AS BoQty
		,A.PlantCode AS PlantCode
		,A.OrderNo AS OrderNo
FROM
(
	SELECT COALESCE(Q.ProductCode, M.ProductId) AS ProductCode
			, COALESCE(Q.PlantCode, M.PlantCode) AS PlantCode
			, COALESCE(Q.OrderNo, M.OrderNo) AS OrderNo
			, COALESCE(Q.Qty, 0) AS QueueQty
			, COALESCE(M.Qty, 0) AS QueueMappedQty
	FROM
	(
		(SELECT ProductCode, PlantCode, OrderNo, Sum(Qty) AS Qty FROM dbo.ProdQueue GROUP BY ProductCode, PlantCode, OrderNo) Q
			FULL OUTER JOIN 
		(SELECT ProductId, PlantCode, OrderNo, Sum(Qty) AS Qty FROM ProductQueueMapped GROUP BY ProductId, PlantCode, OrderNo) M 
			ON Q.ProductCode = M.ProductId AND Q.PlantCode = M.PlantCode AND Q.OrderNo = M.OrderNo
	)
	
) A
INNER JOIN dbo.tbl_OrderDetail D 
	ON A.ProductCode = D.MaterialNo AND A.OrderNo = D.OrderNo

GO


